package com.bee.service;

import java.util.ArrayList;
import java.util.List;

import com.bee.dao.BaseDaoImpl;
import com.bee.pojo.Level;
import com.bee.pojo.User;

/**
 * 在Service实现类中实现业务逻辑
 * 
 * 只专注于SQL语言本身
 * 
 * @author beeworkshop
 *
 */
public class UserServiceImpl extends BaseDaoImpl implements UserService {

	@Override
	public int addUser(User user) {
		String sql = "insert into t_user values(null,?,?,?,?,?)";
		Object[] params = new Object[] { user.getNickname(), user.getRegtime(), user.getPoints(), user.getGender(),
				user.getCid() };
		return execUpdate(sql, params);
	}

	@Override
	public int delUserById(int id) {
		String sql = "delete from t_user where id=?";
		Object[] params = new Object[] { id };
		return execUpdate(sql, params);
	}

	@Override
	public int modifyUserPointsById(int id, int points) {
		String sql = "update t_user set points=? where id=?";
		Object[] params = new Object[] { points, id };
		return execUpdate(sql, params);
	}

	// 实现动态SQL：and
	@Override
	public List<User> getUsers4And(User user) {
		if (user == null) {
			return null;
		}

		List pl = new ArrayList();
		StringBuilder sb = new StringBuilder("select * from t_user where 8=8");

		if (user.getId() > 0) {
			sb.append(" and id=?");
			pl.add(user.getId());
		}
		if (user.getNickname() != null && !user.getNickname().equals("")) {
			sb.append(" and nickname like ?");
			pl.add("%" + user.getNickname() + "%"); // 模糊查询
		}
		if (user.getRegtime() != null && !user.getRegtime().equals("")) {
			sb.append(" and regtime like ?");
			pl.add("%" + user.getRegtime() + "%");// 模糊查询
		}
		if (user.getPoints() > 0) {
			sb.append(" and points=?");
			pl.add(user.getPoints());
		}
		if (user.getGender() != null && !user.getGender().equals("")) {
			sb.append(" and gender=?");
			pl.add(user.getGender());
		}

		return find(sb.toString(), pl.toArray(), User.class);
	}

	// 实现动态SQL：or
	@Override
	public List<User> getUsers4Or(User user) {
		if (user == null) {
			return null;
		}

		List pl = new ArrayList();
		StringBuilder sb = new StringBuilder("select * from t_user");

		if (user.getId() > 0) {
			sb.append(" or id=?");
			pl.add(user.getId());
		}
		if (user.getNickname() != null && !user.getNickname().equals("")) {
			sb.append(" or nickname like ?");
			pl.add("%" + user.getNickname() + "%"); // 模糊查询
		}
		if (user.getRegtime() != null && !user.getRegtime().equals("")) {
			sb.append(" or regtime like ?");
			pl.add("%" + user.getRegtime() + "%");// 模糊查询
		}
		if (user.getPoints() > 0) {
			sb.append(" or points=?");
			pl.add(user.getPoints());
		}
		if (user.getGender() != null && !user.getGender().equals("")) {
			sb.append(" or gender=?");
			pl.add(user.getGender());
		}

		String sql = sb.toString().replaceFirst("or", "where"); // 这里要链式调用replaceFirst()才起作用

		return find(sql, pl.toArray(), User.class);
	}

	@Override
	public List<User> getPointsGtVal(int points) {
		String sql = "select * from t_user where points > ?";
		Object[] params = new Object[] { points };
		return find(sql, params, User.class);
	}

	// 多表连接查询
	@Override
	public List<Level> getUsersMulti() {
		String sql = "SELECT u.*,l.id lId, l.level FROM t_user u, t_level l WHERE u.cid=l.id";
		return find(sql, null, Level.class);
	}

}
